package com.fjh.springboot_01.mapper;


import com.fjh.springboot_01.pojo.tutor.TutoringSchedule;
import org.apache.ibatis.annotations.*;
import java.util.List;
import java.util.Map;
import java.time.LocalDateTime;

@Mapper
public interface TutoringScheduleMapper {
    @Insert("INSERT INTO tutoring_schedule (tutoring_id, teacher_id, student_id, start_time, end_time, " +
            "location, requirements, status, created_by, create_time, update_time) " +
            "VALUES (#{tutoringId}, #{teacherId}, #{studentId}, #{startTime}, #{endTime}, " +
            "#{location}, #{requirements}, #{status}, #{createdBy}, NOW(), NOW())")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    void insert(TutoringSchedule schedule);

    @Select("SELECT ts.*, u1.username as teacher_name, u2.username as student_name, t.title as tutoring_title " +
            "FROM tutoring_schedule ts " +
            "LEFT JOIN user u1 ON ts.teacher_id = u1.id " +
            "LEFT JOIN user u2 ON ts.student_id = u2.id " +
            "LEFT JOIN tutoring t ON ts.tutoring_id = t.id " +
            "WHERE ts.teacher_id = #{teacherId} AND (ts.teacher_deleted IS NULL OR ts.teacher_deleted = 0) " +
            "ORDER BY ts.start_time DESC")
    @Results({
        @Result(column = "teacher_name", property = "teacherName"),
        @Result(column = "student_name", property = "studentName"),
        @Result(column = "tutoring_title", property = "tutoringTitle")
    })
    List<TutoringSchedule> selectByTeacherId(Integer teacherId);

    @Select("SELECT ts.*, u1.username as teacher_name, u2.username as student_name, t.title as tutoring_title " +
            "FROM tutoring_schedule ts " +
            "LEFT JOIN user u1 ON ts.teacher_id = u1.id " +
            "LEFT JOIN user u2 ON ts.student_id = u2.id " +
            "LEFT JOIN tutoring t ON ts.tutoring_id = t.id " +
            "WHERE ts.student_id = #{studentId} AND (ts.student_deleted IS NULL OR ts.student_deleted = 0) " +
            "ORDER BY ts.start_time DESC")
    @Results({
        @Result(column = "teacher_name", property = "teacherName"),
        @Result(column = "student_name", property = "studentName"),
        @Result(column = "tutoring_title", property = "tutoringTitle")
    })
    List<TutoringSchedule> selectByStudentId(Integer studentId);

    @Select("SELECT ts.*, u1.username as teacher_name, u2.username as student_name, t.title as tutoring_title " +
            "FROM tutoring_schedule ts " +
            "LEFT JOIN user u1 ON ts.teacher_id = u1.id " +
            "LEFT JOIN user u2 ON ts.student_id = u2.id " +
            "LEFT JOIN tutoring t ON ts.tutoring_id = t.id " +
            "WHERE ts.id = #{id}")
    @Results({
        @Result(column = "teacher_name", property = "teacherName"),
        @Result(column = "student_name", property = "studentName"),
        @Result(column = "tutoring_title", property = "tutoringTitle")
    })
    TutoringSchedule selectById(Integer id);

    @Update("UPDATE tutoring_schedule SET status = #{status}, update_time = NOW() WHERE id = #{id}")
    void updateStatus(@Param("id") Integer id, @Param("status") Integer status);

    @Update("UPDATE tutoring_schedule SET feedback = #{feedback}, rating = #{rating}, " +
            "status = 2, update_time = NOW() WHERE id = #{id}")
    void updateFeedback(@Param("id") Integer id, @Param("feedback") String feedback, @Param("rating") Integer rating);

    @Select("SELECT ts.id, ts.tutoring_id as tutoringId, ts.teacher_id as teacherId, ts.student_id as studentId, " +
            "ts.start_time as startTime, ts.end_time as endTime, " +
            "ts.location, ts.requirements, ts.status, ts.created_by as createdBy, " +
            "u1.username as teacherName, t.title as tutoringTitle " +
            "FROM tutoring_schedule ts " +
            "LEFT JOIN user u1 ON ts.teacher_id = u1.id " +
            "LEFT JOIN tutoring t ON ts.tutoring_id = t.id " +
            "WHERE ts.student_id = #{studentId} AND ts.status = 0 " +
            "AND (ts.student_deleted IS NULL OR ts.student_deleted = 0) " +
            "ORDER BY ts.create_time DESC")
    List<Map<String, Object>> getStudentPendingSchedules(Integer studentId);

    @Update("UPDATE tutoring_schedule SET teacher_deleted = 1 WHERE id = #{id}")
    void markTeacherDeleted(Integer id);

    @Update("UPDATE tutoring_schedule SET student_deleted = 1 WHERE id = #{id}")
    void markStudentDeleted(Integer id);

    @Delete("DELETE FROM tutoring_schedule WHERE id = #{id} AND teacher_deleted = 1 AND student_deleted = 1")
    void deleteById(Integer id);

    @Update("UPDATE tutoring_schedule SET teacher_deleted = 0 WHERE id = #{id}")
    void restoreTeacherDelete(Integer id);

    @Update("UPDATE tutoring_schedule SET student_deleted = 0 WHERE id = #{id}")
    void restoreStudentDelete(Integer id);

    @Select("SELECT ts.*, u1.username as teacher_name, u2.username as student_name, t.title as tutoring_title " +
            "FROM tutoring_schedule ts " +
            "LEFT JOIN user u1 ON ts.teacher_id = u1.id " +
            "LEFT JOIN user u2 ON ts.student_id = u2.id " +
            "LEFT JOIN tutoring t ON ts.tutoring_id = t.id " +
            "WHERE ts.teacher_id = #{teacherId} " +
            "AND (ts.start_time <= #{endTime} AND ts.end_time >= #{startTime}) " +
            "AND (ts.status = 0 OR ts.status = 1)")
    @Results({
        @Result(column = "teacher_name", property = "teacherName"),
        @Result(column = "student_name", property = "studentName"),
        @Result(column = "tutoring_title", property = "tutoringTitle")
    })
    List<TutoringSchedule> checkTimeConflict(Integer teacherId, LocalDateTime startTime, LocalDateTime endTime);
    
    @Select("SELECT ts.*, u1.username as teacher_name, u2.username as student_name, t.title as tutoring_title " +
            "FROM tutoring_schedule ts " +
            "LEFT JOIN user u1 ON ts.teacher_id = u1.id " +
            "LEFT JOIN user u2 ON ts.student_id = u2.id " +
            "LEFT JOIN tutoring t ON ts.tutoring_id = t.id " +
            "WHERE ts.teacher_id = #{teacherId} " +
            "AND ((ts.start_time BETWEEN #{startDate} AND #{endDate}) " +
            "OR (ts.end_time BETWEEN #{startDate} AND #{endDate}) " +
            "OR (#{startDate} BETWEEN ts.start_time AND ts.end_time)) " +
            "AND (ts.teacher_deleted IS NULL OR ts.teacher_deleted = 0)")
    @Results({
        @Result(column = "teacher_name", property = "teacherName"),
        @Result(column = "student_name", property = "studentName"),
        @Result(column = "tutoring_title", property = "tutoringTitle")
    })
    List<TutoringSchedule> getSchedulesByDateRange(Integer teacherId, LocalDateTime startDate, LocalDateTime endDate);
} 